<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class StatisticsOther extends AdminController {

	function __construct() {
		parent::__construct();
		$this -> load-> model('Student_feedback_model');
		$this -> load-> model('Supervisor_feedback_model');
		$this -> load-> model('Admin_model');
		$this -> load-> model('Table1_model');
		$this -> load-> model('Table2_model');
		$this -> load-> model('Table3_model');
		$this -> load-> model('Table4_model');
		$this -> load-> model('Table5_model');
		$this -> load-> model('Table6_model');
	}

	function Index() {	
		$this->load->view('admin/statisticsOtherList');
	}

	function getDatas($params = array(), $isExport = false) {
		$connar = array();
		$params = $_POST;
		if (!$isExport) {			
			$connar = $this -> Admin_model -> pickPages($connar, $params);
		}	
		$connar['items']="DISTINCT (db_admin.id),usercode,username";
		$connar['join'][0]=array('db_table1','db_table1.admin_id=db_admin.id');
		$connar['join'][1]=array('db_table2','db_table2.admin_id=db_admin.id');
		$connar['join'][2]=array('db_table3','db_table3.admin_id=db_admin.id');
		$connar['join'][3]=array('db_table4','db_table4.admin_id=db_admin.id');
		$connar['join'][4]=array('db_table5','db_table5.admin_id=db_admin.id');
		$connar['join'][5]=array('db_table6','db_table6.admin_id=db_admin.id');
		$connar['where']=$this->sqlEscape('db_admin.status<>?? and (db_table1.admin_id IS NOT NULL OR db_table2.admin_id IS NOT NULL OR db_table3.admin_id IS NOT NULL OR db_table4.admin_id IS NOT NULL 	OR db_table5.admin_id IS NOT NULL)',array(CommonStatus::Deleted[XPHEnum::Value]));
		$role=explode(',',getSess()['sub_role_id']);
		array_push($role,getSess()['role_id']);
		if(in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
			$connar['where'].=' and 1=1 ';
		}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
			$connar['where'].=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
		}else if(in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::ExternalSupervisor[XPHEnum::Value], $role)||in_array(WebRole::SupervisorDirector[XPHEnum::Value], $role)){
			$connar['where'].=$this->sqlEscape(' and db_admin.id=?? ',array(getSess()['id']));
		}
		if (isset($params['dept_name'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and dept_name like "%??%"',array($params['dept_name']));
		}else if (isset($_POST['dept_name'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and dept_name like "%??%"',array($_POST['dept_name']));
		}
		if (isset($params['username'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and username like "%??%"',array($params['username']));
		}else if (isset($_POST['username'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and username like "%??%"',array($_POST['username']));
		}
		if (isset($params['level']) && $params['level']!=-1) {
			if($params['level']==FeedBackSubType::School[XPHEnum::Value]){
				$level=WebRole::SchoolSupervisor[XPHEnum::Value];
			}
			if($params['level']==FeedBackSubType::Unit[XPHEnum::Value]){
				$level=WebRole::UnitSupervisor[XPHEnum::Value];
			}
			$connar['where'].=$this ->sqlEscape(' and (role_id=?? or find_in_set(??,sub_role_id))',array($level,$level));
		}else if (isset($_POST['level']) && $_POST['level']!=-1) {
			if($_POST['level']==FeedBackSubType::School[XPHEnum::Value]){
				$level=WebRole::SchoolSupervisor[XPHEnum::Value];
			}
			if($_POST['level']==FeedBackSubType::Unit[XPHEnum::Value]){
				$level=WebRole::UnitSupervisor[XPHEnum::Value];
			}
			$connar['where'].=$this ->sqlEscape(' and (role_id=?? or find_in_set(??,sub_role_id))',array($level,$level));
		}	

		if (isset($params['start_time']) && isset($params['end_time'])) {
			// $where.=' and created>'.strtotime($params['start_time']).' and created<'.strtotime($params['end_time']);
			// $connar['where'].=$this->sqlEscape(" and db_table1.created>=?? and db_table1.created<??  and db_table2.created>=?? and db_table2.created<?? and db_table3.created>=?? and db_table3.created<?? and db_table4.created>=?? and db_table4.created<?? and db_table5.created>=?? and db_table5.created<?? and db_table6.created>=?? and db_table6.created<??",array(strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59')));
		}
		

		if (!$isExport) {
			$data = $this -> Admin_model -> get_page_list($connar);
			// 重新计算条数
			$count=$this->Admin_model->query("SELECT count(*) count from (SELECT DISTINCT 	db_admin.`usercode`,db_admin.`id`,	db_admin.`username` FROM `db_admin` LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id  LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id WHERE ".$connar['where'].")a");
			$data['count']=$count[0]['count'];
			$data["totalPage"] = ceil($data["count"] / $params['pageSize']);
		} else {
			$data["ret"] = $this -> Admin_model -> get_list_full($connar);
		}

		
		foreach ($data['ret'] as $k => $v) {	
			// 反馈次数
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where=' and feedback.created>'.strtotime($params['start_time']).' and feedback.created<'.strtotime($params['end_time']);
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])){
				$where=' and feedback.created>'.strtotime($_POST['start_time']).' and feedback.created<'.strtotime($_POST['end_time']);
			}else{
				$where='';
			}
			$feedbackTime=$this -> Student_feedback_model->query("select count(*) feedbackTime from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=".CommonStatus::UnDeleted[XPHEnum::Value]." and db_admin.id=".$v['id'].$where);

			$data['ret'][$k]['feedbackTime']=$feedbackTime[0]['feedbackTime'];	
			// 教学督导专题教研次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table1Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table1Time=$this-> Table1_model->get_list_full($connar1);
			$data['ret'][$k]['table1Time']=$table1Time[0]['table1Time'];
			// 督导教学巡(检)查情况记录表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table2Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}
			$table2Time=$this-> Table2_model->get_list_full($connar1);
			$data['ret'][$k]['table2Time']=$table2Time[0]['table2Time'];
			// 督导巡考情况记录表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table3Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table3Time=$this-> Table3_model->get_list_full($connar1);
			$data['ret'][$k]['table3Time']=$table3Time[0]['table3Time'];
			// 教学状态专项检查表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table4Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table4Time=$this-> Table4_model->get_list_full($connar1);
			$data['ret'][$k]['table4Time']=$table4Time[0]['table4Time'];
			// 学生顶岗实习巡查次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table5Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table5Time=$this-> Table5_model->get_list_full($connar1);
			$data['ret'][$k]['table5Time']=$table5Time[0]['table5Time'];
			// 教学信息员专题调查表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table6Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table6Time=$this-> Table6_model->get_list_full($connar1);
			$data['ret'][$k]['table6Time']=$table6Time[0]['table6Time'];
		}
		if (!$isExport) {
			echo json_encode($data);			
		} else {
			// return $data["ret"];			
			$this-> load ->library('PHPExcel/PHPExcel');
	        $excel = new PHPExcel();
	        //公共样式
	        $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
	        $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

	        $x_line=0;
	        $y_line=1;

			//标题
	        $title=array($_POST['start_time'].'至'.$_POST['end_time'],'工号','姓名','反馈次数','教学督导专题调研次数','督导教学巡(检)查情况记录表次数','督导巡考情况记录表次数','教学状态专项检查表次数','学生顶岗实习巡查次数','教学信息员专题调查表次数');
	        foreach ($title as $k => $v) {
	        	$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v);
	        	$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line)->getFont()->setBold(true);   
	        	$x_line++;
	        }

	        $x_line=0;
	        $y_line=2;
	        //正式数据
	        
	        foreach ($data['ret'] as $k => $v) {        	
	        	foreach ($v as $k1 => $v1) {
	        		if($k1=='id'){
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$_POST['start_time'].'至'.$_POST['end_time']);
	        		}else{
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v1);
	        		}
	        		
	        		$x_line++;
	        	}
	        	$x_line=0;
	        	$y_line++;
	        }
	        $write = new PHPExcel_Writer_Excel2007($excel); 
	       	$director = "upload/export/" . date('Ymd', time()) . "/";
	        if (!file_exists($director)) {
	            //检查是否有该文件夹，如果没有就创建，并给予最高权限
	            mkdir($director, 0777);
	        }        
			$file_name ='个人其他材料反馈报表'.date('Ymd').'.xlsx';       	
	       	$write->save($director.iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址',true,$director.$file_name));
		}
	}
}
